/*** 对数据表的增删改查方法，table.go包访问session.go包 ***/
package db

//biu是查询组合体
import (
	"errors"
	"fmt"
	"../conv"
	"math"
	"strings"
)

/*表引擎*/
type Table struct {
	Session *Session
	args    []interface{} //where使用
	table   string        //表名
	field   string        //查询字段
	sql     string        //sql语句
	where   bool          //
	data    interface{}   //需要映射数据的对象或者数据
}

func newTable(sx *Session, table string) *Table {
	o := new(Table)
	o.Session = sx
	o.args = make([]interface{}, 0)
	o.field = " * "
	o.table = table
	return o
}

//删除表格
func (this *Table) Drop() error {
	_, e := this.Session.Exec("DROP TABLE  `" + this.table + "`")
	return e
}

//创建表格
func (this *Table) Create(data H, other, engine string) error {
	sqlArr := make([]string, 0)
	sqlArr = append(sqlArr, "CREATE TABLE IF NOT EXISTS `", this.table, "`(")
	for col, tp := range data {
		if colType, ok := tp.(string); ok {
			sqlArr = append(sqlArr, " `", col, "` ", colType, ",")
		}
	}
	//清除最后一个逗号
	sqlArr = sqlArr[:len(sqlArr)-1]
	sqlArr = append(sqlArr, other, ")ENGINE=", engine, " DEFAULT CHARSET=utf8")
	sql := strings.Join(sqlArr, "")
	_, e := this.Session.Exec(sql)
	return e
}

//表结构
func (this *Table) ShowColumns() ([]map[string]string, error) {
	return this.Session.Query("SHOW COLUMNS FROM `" + this.table + "`")
}

//拷贝一个对象
func (this *Table) Copy() *Table {
	o := *this
	return &o
}

//表结构同步
func (this *Table) Sync(data H) error {
	return nil
}

//需要查询的字段
func (this *Table) Field(colums string) *Table {
	this.field = colums
	return this
}

//where查询
func (this *Table) Where(query string, args ...interface{}) *Table {
	this.where = true
	query = "WHERE " + query
	return this.Query(query, args...)
}

//and查询
func (this *Table) And(query string, args ...interface{}) *Table {
	query = "AND (" + query + " ) "
	return this.Query(query, args...)
}

//or查询
func (this *Table) Or(query string, args ...interface{}) *Table {
	query = "OR (" + query + " ) "
	return this.Query(query, args...)
}

//sql里面放column
func (this *Table) In(sql string, args ...interface{}) *Table {
	b := make([]interface{}, 0)
	if !this.where {
		b = append(b, " WHERE ")
	}
	b = append(b, " ", sql, " IN(")
	l := len(args)
	for k, v := range args {
		b = append(b, "\"", v, "\"")
		if k+1 < l {
			b = append(b, ",")
		}
	}
	b = append(b, ")")
	this.sql += fmt.Sprint(b...)
	return this
}

//排序
func (this *Table) OrderBy(sql string) *Table {
	this.sql += " ORDER BY " + sql
	return this
}

//sql条件（更自由吧),sql语句中使用?,不要使用%s
func (this *Table) Query(sql string, args ...interface{}) *Table {
	this.sql += " " + sql
	this.args = append(this.args, args...)
	return this
}

/*
插入数据，返回自增长id，可以输入一维map也可以输入2维map
*/
func (this *Table) Insert(data H) (insertId int64, err error) {
	sqlStr, sqlArgs := this.make1map2insert(data)
	sqlStr = "INSERT INTO " + this.table + sqlStr
	sqlArgs = append(sqlArgs, this.args...)
	result, err := this.Session.Exec(sqlStr, sqlArgs...)
	//fmt.Println("打印sql语句：", sqlStr, sqlArgs)
	if err != nil {
		return
	}
	return result.LastInsertId()
}

//插入多条数据
func (this *Table) InsertMuti(data []H) (int64, error) {
	sqlStr, sqlArgs := this.make2map2insert(data)
	sqlStr = "INSERT INTO " + this.table + sqlStr
	sqlArgs = append(sqlArgs, this.args...)
	result, err := this.Session.Exec(sqlStr, sqlArgs...)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

//字段长度
func (this *Table) Count() int32 {
	sqlStr := "SELECT COUNT(*) as num From " + this.table + this.sql
	result, err := this.Session.Query(sqlStr, this.args...)
	fmt.Println(err)
	if err != nil {
		return 0
	}
	return conv.MustInt32(string(result[0]["num"]), 0)
}

//查询
func (this *Table) Get() ([]map[string]string, error) {
	this.sql = fmt.Sprintf("SELECT %s FROM %s %s ", this.field, this.table, this.sql)
	//fmt.Println("打印sql语句：", this.sql, this.args)
	return this.Session.Query(this.sql, this.args...)
}

//查询一条数据
func (this *Table) GetOne() (map[string]string, error) {
	this.sql += " LIMIT 1"
	res, err := this.Get()
	if err != nil {
		return nil, err
	}
	l := len(res)

	if l == 1 {
		return res[0], nil
	} else if l > 1 {
		return nil, errors.New("data res is not one.")
	}
	return nil, errors.New("no data.")
}

//查询分页
func (this *Table) GetPage(current, limit int) (r []map[string]string, p *Page, e error) {
	if current == 0 {
		current = 1
	}
	if limit == 0 {
		limit = 10
	}
	total := this.Count()
	p = new(Page)
	p.Pages = int(math.Ceil(float64(total) / float64(limit))) //计算出页码
	if current <= 1 {                                         //页首不越界
		current = 1
	} else if current >= p.Pages { //页末不越界
		current = p.Pages
	}
	p.Total = int(total)
	p.Limit = limit
	p.Current = current
	start := (current - 1) * limit
	sqls := fmt.Sprintf("SELECT %s FROM %s %s limit %d,%d ", this.field, this.table, this.sql, start, limit)
	//fmt.Println("打印sql语句：", sqls, this.args)
	result, err := this.Session.Query(sqls, this.args...)
	return result, p, err
}

/*
修改数据，返回受影响的行数
*/
func (this *Table) Update(data H) (aff int64, err error) {
	sqlStr, sqlArgs := this.map2update(data)
	sqlStr = "UPDATE " + this.table + " SET " + sqlStr + this.sql
	sqlArgs = append(sqlArgs, this.args...)
	//fmt.Println("打印sql语句：", sqlStr, sqlArgs)
	result, err := this.Session.Exec(sqlStr, sqlArgs...)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

/*
修改单条数据，返回受影响的行数
*/
func (this *Table) UpdateOne(data H) (aff int64, err error) {
	this.sql = this.sql + " LIMIT 1"
	return this.Update(data)
}

/*
删除数据，返回受影响的行数
*/
func (this *Table) Delete() (aff int64, err error) {
	sqlStr := "DELETE  FROM " + this.table + this.sql
	result, err := this.Session.Exec(sqlStr, this.args...)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

/*
删除单条数据，返回受影响的行数
*/
func (this *Table) DeleteOne() (aff int64, err error) {
	this.sql = this.sql + " LIMIT 1"
	return this.Delete()
}

/*
map转换为修改sql语句
*/
func (this *Table) map2update(data H) (sql string, args []interface{}) {
	var sqlArr []string
	for k, v := range data {
		v = conv.MustString(v, "")
		if v == "" {
			continue
		}
		sqlArr = append(sqlArr, "`"+k+"`=?")
		args = append(args, v)
	}
	sql = strings.Join(sqlArr, ",")
	return
}

//1维map
func (this *Table) make1map2insert(arrData H) (sql string, args []interface{}) {
	var colsArr, valueArr []string
	var sqlCols, sqlValue string
	for k, v := range arrData {
		v = conv.MustString(v, "")
		if v == "" {
			continue
		}
		colsArr = append(colsArr, "`"+k+"`")
		valueArr = append(valueArr, "?")
		args = append(args, v)
	}
	sqlValue = "(" + strings.Join(valueArr, ",") + ")"
	sqlCols = strings.Join(colsArr, ",")
	sql = "(" + sqlCols + ") values  " + sqlValue
	return
}

//2维map
func (this *Table) make2map2insert(arrData []H) (sql string, args []interface{}) {
	var colsArr, valueArr []string
	var sqlCols, sqlValue string
	var cols []string //map数据的列
	for index, brr := range arrData {
		if index == 0 {
			for k, _ := range brr {
				colsArr = append(colsArr, "`"+k+"`")
				cols = append(cols, k)
			}
		}
		var valueBrr []string
		for _, k := range cols {
			valueBrr = append(valueBrr, "?")
			args = append(args, brr[k])
		}
		value := "(" + strings.Join(valueBrr, ",") + ")"
		valueArr = append(valueArr, value)
	}
	sqlValue = strings.Join(valueArr, ",")
	sqlCols = strings.Join(colsArr, ",")
	sql = "(" + sqlCols + ") values  " + sqlValue
	return
}
